﻿using System;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class People_Import : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    // For admin pages, check the current user is an admin person
    if (!Conn.IsAdminUser())
    {
      Response.Redirect("Default.aspx");
    }
  }
  protected void bnImport_Click(object sender, EventArgs e)
  {
    if (filImport.HasFile)
    {
      byte[] bytes = filImport.FileBytes;
      String page = System.Text.Encoding.ASCII.GetString(bytes);
      using (SqlConnection conn = Conn.GetOpened())
      {
        string sql = @"
INSERT INTO people
           ([created]
           ,[ip_address]
           ,[uid]
           ,[username]
           ,[first_name]
           ,[last_name]
           ,[email]
           ,[cell_mobile]
           ,[street]
           ,[city]
           ,[state]
           ,[postcode]
           ,[country]
           ,[home_phone]
           ,[age]
           ,[gender])
     VALUES
           (@created
           ,@ip_address
           ,@uid
           ,@username
           ,@first_name
           ,@last_name
           ,@email
           ,@cell_mobile
           ,@street
           ,@city
           ,@state
           ,@postcode
           ,@country
           ,@home_phone
           ,@age
           ,@gender)
";
        SqlCommand cmd = new SqlCommand(sql, conn);

        foreach (string row in page.Split('\r', '\n'))
        {
          if (row.Length > 0)
          {
            string[] cols = row.Split('\t');
            if (cols.Length >= 9) // 9 is because we need at least up to the email address
            {
              int serialI;
              string serialS = GetCol(cols, 0) as string;
              if (serialS != null && int.TryParse(serialS, out serialI)) // the first column must be an integer serial #
              {
                // OK import this row!
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@created", GetCol(cols, 2));
                cmd.Parameters.AddWithValue("@ip_address", GetCol(cols, 3));
                cmd.Parameters.AddWithValue("@uid", GetCol(cols, 4));
                cmd.Parameters.AddWithValue("@username", GetCol(cols, 5));
                cmd.Parameters.AddWithValue("@first_name", GetCol(cols, 6));
                cmd.Parameters.AddWithValue("@last_name", GetCol(cols, 7));
                cmd.Parameters.AddWithValue("@email", GetCol(cols, 8));
                cmd.Parameters.AddWithValue("@cell_mobile", GetCol(cols, 9));
                cmd.Parameters.AddWithValue("@street", GetCol(cols, 10));
                cmd.Parameters.AddWithValue("@city", GetCol(cols, 11));
                cmd.Parameters.AddWithValue("@state", GetCol(cols, 12));
                cmd.Parameters.AddWithValue("@postcode", GetCol(cols, 13));
                cmd.Parameters.AddWithValue("@country", GetCol(cols, 14));
                cmd.Parameters.AddWithValue("@home_phone", GetCol(cols, 15));
                cmd.Parameters.AddWithValue("@age", GetCol(cols, 16));
                cmd.Parameters.AddWithValue("@gender", GetCol(cols, 17));
                cmd.ExecuteNonQuery();
              }
            }
          }
        }
      }
      Response.Redirect("People.aspx");
    }
  }

  object GetCol(string[] columns, int index)
  {
    if (index >= columns.Length) return DBNull.Value;
    string s = columns[index].Trim();
    if (s.Length>=2 && s.StartsWith("\"") && s.EndsWith("\"")) s = s.Substring(1, s.Length - 2).Trim();
    if (s.Length == 0) return DBNull.Value;
    return s;
  }

  protected void bnImport2_Click(object sender, EventArgs e)
  {
    if (filImport2.HasFile)
    {
      byte[] bytes = filImport2.FileBytes;
      String page = System.Text.Encoding.ASCII.GetString(bytes);
      using (SqlConnection conn = Conn.GetOpened())
      {
        string sql = @"
INSERT INTO people
           ([first_name]
           ,[last_name]
           ,[email]
           ,[state]
           ,[notes])
     VALUES
           (@first_name
           ,@last_name
           ,@email
           ,@state
           ,@notes)
";
        SqlCommand cmd = new SqlCommand(sql, conn);

        foreach (string row in page.Split('\r', '\n'))
        {
          if (row.Length > 0)
          {
            string[] cols = row.Split('\t');
            if (cols.Length >= 3) // 3 is because we need at least up to the email address
            {
              if (cols[2].Contains('@')) // The email column must have an email address
              {
                // OK import this row!
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@first_name", GetCol(cols, 0));
                cmd.Parameters.AddWithValue("@last_name", GetCol(cols, 1));
                cmd.Parameters.AddWithValue("@email", GetCol(cols, 2));
                cmd.Parameters.AddWithValue("@state", GetCol(cols, 3));
                cmd.Parameters.AddWithValue("@notes", GetCol(cols, 4));
                cmd.ExecuteNonQuery();
              }
            }
          }
        }
      }
      Response.Redirect("People.aspx");
    }
  }

  /// <summary>
  /// Converts bytes to a string, autodetecting the encoding
  /// </summary>
  string BytesToTextAutoEncoding(byte[] data)
  {
    // Save to a temp file
    string file = "";
    try {
      file = Path.GetTempFileName();
      File.WriteAllBytes(file, data);
      return File.ReadAllText(file); // This is where it auto-detects encoding
    }
    finally {
      File.Delete(file);
    }
  }

  /// <summary>
  /// Imports from a universal template
  /// First row is the header, the rest are data
  /// </summary>
  protected void bnImportUniversal_Click(object sender, EventArgs e)
  {
    if (filUniversalImport.HasFile)
    {
      // Grab it as a string
      String page = BytesToTextAutoEncoding(filUniversalImport.FileBytes);
      // Convert it into individual lines
      string[] rows = page.Split(new char[]{'\r', '\n'}, StringSplitOptions.RemoveEmptyEntries);
      // Get the fields from the first line
      string[] fields = rows[0].Split('\t');
      // Make a list of fields with the '@' before them
      string[] parameters = (from f in fields select "@" + f).ToArray();

      // Make the sql
      string sql =
        "insert into people (" +
        string.Join(",",fields) + 
        ") values (" +
        string.Join(",",parameters) + 
        ");";

      // Go thru the data lines one at a time
      using (SqlConnection conn = Conn.GetOpened())
      {
        SqlCommand cmd = new SqlCommand(sql, conn); // Create the command we re-use for each row
        for (int r = 1; r < rows.Length; r++) // Loop thru rows, skipping the header row
        {
          string row = rows[r]; // Get the current row
          if (row.Length > 0) // Check its not empty
          {
            string[] cols = row.Split('\t'); // Get the columns in this row
            // Now fill the params into the reused command
            cmd.Parameters.Clear();
            for (int p = 0; p < parameters.Length; p++)
            {
              cmd.Parameters.AddWithValue(parameters[p], GetCol(cols, p));
            }
            // Save this row to the db
            cmd.ExecuteNonQuery();
          }
        }
      }
      Response.Redirect("People.aspx");
    }
  }

  /// <summary>
  /// Generate the universal template and give it to the user
  /// </summary>
  protected void bnUniversalTemplate_Click(object sender, EventArgs e)
  {
    // Get the list of columns from the people table
    List<String> fields = new List<string>();
    using (SqlConnection conn = Conn.GetOpened())
    {
      using (SqlCommand cmd = new SqlCommand("select * from people", conn))
      {
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
          for (int i = 0; i < rdr.FieldCount; i++) // Go thru all the fields without actually reading any rows
          {
            string field = rdr.GetName(i);
            if (field != "id") // we don't want the id field
            {
              fields.Add(field);
            }
          }
        }
      }
    }

    // Create the TSV file
    StringBuilder sb = new StringBuilder();
    foreach (string field in fields) // Make the row with all the field names
    {
      sb.Append(field).Append('\t');
    }
    sb.AppendLine();
    for (int i=0;i<10;i++) // Make 10 rows of sample data
    {
      foreach (string field in fields) // Make the row with all the 'example'
      {
        sb.Append("<replace>").Append('\t');
      }
      sb.AppendLine();
    }

    // Return it as a download to the user
    Response.Clear();
    Response.ContentType = "text/csv";
    Response.AppendHeader("Content-Disposition", "attachment; filename=ImportTemplate.xls");
    Response.Write(sb.ToString());
    Response.End();
  }
}
